﻿using System.Data;
using System.Text;
using ZeBobo5.LibraryHelper.Core.Sql;

namespace ZeBobo5.LibraryHelper.Orm
{
    public class OrmDataFactory<A>
        where A : OrmDataElementBase<A>, new()
    {
        private OrmOrderByFactory<A> _oderBy;
        private string _strConnectionName;
        private OrmWhereFactory _where;
        private bool _distinct = false;
        private OrmFieldBase<A>[] _selectFields;

        private OrmDataFactory()
        {
        }

        public string ConnectionName
        {
            get { return _strConnectionName; }
        }

        public static OrmDataFactory<A> Select(string connectionName)
        {
            return Select(connectionName, null);
        }

        public static OrmDataFactory<A> Select(string connectionName, params OrmFieldBase<A>[] fields)
        {
            OrmDataFactory<A> factory = new OrmDataFactory<A>();
            factory._strConnectionName = connectionName;
            factory._distinct = false;
            factory._selectFields = fields;
            return factory;
        }

        public static OrmDataFactory<A> SelectDistinct(string connectionName, params OrmFieldBase<A>[] fields)
        {
            OrmDataFactory<A> factory = new OrmDataFactory<A>();
            factory._strConnectionName = connectionName;
            factory._distinct = true;
            factory._selectFields = fields;
            return factory;
        }

        public OrmDataFactory<A> Where(OrmWhereFactory whereFactory)
        {
            _where = _where != null ? _where.And(whereFactory) : whereFactory;
            return this;
        }

        public OrmDataFactory<A> OrderBy(OrmOrderByFactory<A> orderByFactory)
        {
            _oderBy = orderByFactory;
            return this;
        }

        

        public OrmDataElementList<A> Execute()
        {
            StringBuilder query = new StringBuilder();

            //Select / From
            if(_selectFields != null && _selectFields.Length > 0)
            {
                if (_distinct)
                {
                    query.Append("SELECT DISTINCT ");
                }
                else
                {
                    query.Append("SELECT ");
                    
                }
                foreach (OrmFieldBase<A> field in _selectFields)
                {
                    query.AppendFormat("{0}, ", field.FieldName);
                }
                query.Length -= 2;
                query.AppendFormat(" FROM {0} ", typeof(A).Name);
            }
            else
            {
                query.AppendFormat("SELECT * FROM {0} ", typeof(A).Name);
            }

            //Where
            if (_where != null)
            {
                query.AppendFormat("WHERE {0}", _where.SqlText);
            }

            //Order by
            if (_oderBy != null && _oderBy.Fields.Length > 0)
            {
                query.Append("ORDER BY ");
                for (int cpt = 0; cpt < _oderBy.Fields.Length; cpt++)
                {
                    query.Append(_oderBy.Fields[cpt].FieldName);
                    if (cpt != _oderBy.Fields.Length - 1)
                    {
                        query.Append(", ");
                    }
                    else
                    {
                        query.Append(" ");
                    }
                }
                query.Append(_oderBy.OrderType.ToString().ToUpper());
            }

            DataTable table = SqlHelper.ExecQueryToDataTable(_strConnectionName, query.ToString());
            OrmDataElementList<A> lst = new OrmDataElementList<A>();
            foreach (DataRow row in table.Rows)
            {
                A a = new A();
                foreach (DataColumn column in table.Columns)
                {
                    if (a.GetType().GetProperty(column.ColumnName.ToUpper()) != null)
                    {
                        OrmValuesBase v = (OrmValuesBase) a.GetType().GetProperty(column.ColumnName.ToUpper()).GetValue(a, null);
                        v._currentValue = row[column];
                    }
                }
                a._strConnectionName = _strConnectionName;
                lst.AddToList(a);
            }
            lst._strConnectionName = _strConnectionName;
            return lst;
        }
    }
}